JCapper Message Board

JCapper 101
-- SQL

Home	Register Log In

SQL

NYMike
1/12/2015
1:01:21 PM

Jeff,
How would I write the SQL query to identify a horse that won his last race which was a maiden. I can't seem to find the answer.

Thanks,

Mike

jeff
1/13/2015
9:33:36 AM

It's easy.

First, here's a link to the Table Schema Doc.

Let's break it into parts - using one line of sql per part:

• Won Last Race - The data field name (from the Table Schema) for this part is the posfincall field. Horses that won their last start will have a value of 1 in this field. The following line of sql should do the trick:

AND posfincall = 1

• Which was a maiden - The data field name (from the Table Schema) for this part is the classdescrlast field. Horses competing in maiden races their last start will have a value of 'M' in this field if they raced in a Maiden Claimer. Horses that raced in a Maiden Special Weight their last start will have a value of 'S' in this field. The following line of sql should do the trick for either case:

AND (classdescrlast = 'M' OR classdescrlast = 'S')

Note: Placement of parenthesis characters in the above line is crucial.

Hint: Multiple expressions separated by keywords such as OR inside of parenthesis characters are evaluated as a single expression.

Note: The following line of sql also works for the "Which was a maiden" condition:

AND INSTR('M-S', classdescrlast) > 0

Putting it all together, the following sql expression should do the trick:

SELECT * FROM STARTERHISTORY WHERE posfincall = 1
AND (classdescrlast = 'M' OR classdescrlast = 'S')

As should the following alternate expression:

SELECT * FROM STARTERHISTORY WHERE posfincall = 1
AND INSTR('M-S', classdescrlast) > 0

-jp

.

JCapper Message Board

JCapper 101 -- SQL

JCapper 101
-- SQL